<?php
	class logintransactionmodel extends Model {
		function logintransactionmodel()
		{
			parent::Model();			
		}
		
		function add($profile)
		{
			$dataDB = array(
               'username' => $this->session->userdata('username') ,
			   'profile' => $profile
            );
            $this->db->set('login_date', 'NOW()', FALSE); 
			$this->db->insert('login_transaction', $dataDB);
			return $this->db->insert_id();
		}
		
		function getByCriteria($key)
		{
			$sql = "SELECT trans. * , tu.tutor_id, tu.first_name_th AS 'first_name', tu.last_name_th AS 'last_name',  GROUP_CONCAT( sc.school_name ) AS 'school_name' , HOUR(login_date) as 'log_h'
					FROM login_transaction trans, tutor tu, tutor_school ts, school sc 
					WHERE trans.username =  tu.username 
					AND tu.tutor_id =  ts.tutor_id 
					AND ts.shool_id = sc.school_id ";
			 
			if($key['keyword'] != null) {
				$sql = $sql . " AND ( tu.first_name_th LIKE '%" . $key['keyword'] . "%' or  tu.last_name_th LIKE '%" . $key['keyword'] . "%' )";
			}
			if($key['profile'] != null) {
				$sql = $sql . " and profile = '" . $key['profile'] . "' ";
			}		
			if($key['times'] != null) {
				if($key['times'] == 1) 
					$sql = $sql . " and  HOUR(login_date) BETWEEN 7 AND 10 ";
					
				if($key['times'] == 2) 
					$sql = $sql . " and HOUR(login_date) BETWEEN 10 AND 16 ";
					
				if($key['times'] == 3) 
					$sql = $sql . " and ( (HOUR(login_date) BETWEEN 16 AND 19) 
					                      or (HOUR(login_date) = 19 and MINUTE(login_date) <= 30) )";
					
				if($key['times'] == 4) 
					$sql = $sql . " and ((HOUR(login_date) = 19 and MINUTE(login_date) BETWEEN 31 AND 59 ) ) ";
					
				if($key['times'] == 5) 
					$sql = $sql . " and HOUR(login_date) BETWEEN 20 AND 21 ";
					
				if($key['times'] == 6) 
					$sql = $sql . " and (( HOUR(login_date) BETWEEN 21 AND 24 ) or ( HOUR(login_date) BETWEEN 0 AND 7 ) )";
			}
			$sql = $sql . " GROUP BY trans.login_date ";		
			$sql = $sql . " UNION SELECT trans. * , e.employee_id, e.first_name, e.last_name, GROUP_CONCAT( sc.school_name ) AS 'school_name', HOUR(login_date) as 'log_h'
					FROM login_transaction trans, employee e, employee_school es, school sc
					WHERE trans.username = e.username
					AND e.employee_id = es.employee_id
					AND es.school_id = sc.school_id ";
			if($key['keyword'] != null) {
				$sql = $sql . "AND ( e.first_name LIKE '%" . $key['keyword'] . "%' or  e.last_name LIKE '%" . $key['keyword'] . "%' )";
			}
			if($key['profile'] != null) {
				$sql = $sql . " and profile = '" . $key['profile'] . "' ";
			}
			if($key['times'] != null) {
				if($key['times'] == 1) 
					$sql = $sql . " and  HOUR(login_date) BETWEEN 7 AND 10 ";
					
				if($key['times'] == 2) 
					$sql = $sql . " and HOUR(login_date) BETWEEN 10 AND 16 ";
					
				if($key['times'] == 3) 
					$sql = $sql . " and ( (HOUR(login_date) BETWEEN 16 AND 19) 
					                      or (HOUR(login_date) = 19 and MINUTE(login_date) <= 30) )";
					
				if($key['times'] == 4) 
					$sql = $sql . " and ((HOUR(login_date) = 19 and MINUTE(login_date) BETWEEN 31 AND 59 ) ) ";
					
				if($key['times'] == 5) 
					$sql = $sql . " and HOUR(login_date) BETWEEN 20 AND 21 ";
					
				if($key['times'] == 6) 
					$sql = $sql . " and (( HOUR(login_date) BETWEEN 21 AND 24 ) or ( HOUR(login_date) BETWEEN 0 AND 7 ) )";
			}
			$sql = $sql . " GROUP BY trans.login_date order by login_date desc ";	
			
			$query = $this->db->query($sql);  
			return $query->result();
		}
	}
?>